package com.viknix.mysql.demo.service.impl;

import com.mysql.jdbc.JDBC4Connection;
import com.viknix.mysql.demo.service.MassDataQueryService;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.Objects;

/**
 * @Author: Dongqi
 * @Date: 2021/11/5 9:16
 * @Version 1.0
 * @Description:
 */
@Slf4j
@Service
public class MassDataQueryServiceImpl implements MassDataQueryService {

    private Connection conn = null;
    private PreparedStatement ps = null;
    private Statement statement = null;
    private ResultSet resultSet = null;
    private long start = 0;

    static {

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    @SneakyThrows
    public void init() {

        conn = DriverManager.getConnection("jdbc:mysql://101.34.50.82:3306/mysql-demo?useCursorFetch=true", "root", "123456");
        start = System.currentTimeMillis();
    }

    @SneakyThrows
    public void after() {

        log.info(">>> 查询耗时 :: {} ms", System.currentTimeMillis() - start);
        if (Objects.nonNull(resultSet)) {
            resultSet.close();
        }
        if (Objects.nonNull(statement)) {
            statement.close();
        }
        if (Objects.nonNull(ps)) {
            ps.close();
        }
        if (Objects.nonNull(conn)) {
            conn.close();
        }
    }

    @SneakyThrows
    @Override
    public void generalQuery() {

        // 1核2G：查询一百条记录：47ms
        // 1核2G：查询一千条记录：1709 ms
        // 1核2G：查询一万条记录：26589 ms
        // 1核2G：查询五万条记录：135966 ms
        init();
        log.info(">>> start general query");
        String sql = "select * from wh_b_inventory limit 50000";
        ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println(count);
        after();
    }

    @SneakyThrows
    @Override
    public void streamQuery() {

        // 1核2G：查询一百条记录：138ms
        // 1核2G：查询一千条记录：1885 ms
        // 1核2G：查询一万条记录：26536 ms
        // 1核2G：查询五万条记录：135931 ms
        init();
        log.info(">>> start stream query");
        String sql = "select * from wh_b_inventory limit 50000";
        statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(Integer.MIN_VALUE);
        ResultSet rs = statement.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println(count);
        after();
    }

    @SneakyThrows
    @Override
    public void cursorQuery() {

        // 1核2G：查询五万条记录：91490 ms
        init();
        log.info(">>> start cursor query");
        String sql = "select * from wh_b_inventory limit 50000";
        ((JDBC4Connection) conn).setUseCursorFetch(true);
        statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(10);
        ResultSet rs = statement.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        System.out.println(count);
        after();
    }
}
